package com.controller.yuejuan;

import com.bean.ScoreBean;
import com.bean.StudentScore;
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.model.Course;
import com.model.PaperConfig;
import com.model.Student;
import com.service.yuejuan.YuejuanService;
import com.utils.ConstData;
import org.apache.poi.hssf.usermodel.*;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;

/**
 * Created by john on 2017/8/1.
 */
public class YueJuanController extends Controller {
    public YuejuanService yuejuanService = new YuejuanService();

    public void showyuejian() {
        List<Record> course1 = Db.find("select DISTINCT courses.name from courses, studentexam " +
                "where courses.id=studentexam.course_id ");
        List<Record> getcourse = Db.find("select DISTINCT courses.`name` from courses");
        List<String> name = new ArrayList<String>();
        List<String> name1 = new ArrayList<String>();
        for (int i = 0; i < course1.size(); i++) {
            name.add(course1.get(i).get("name").toString().substring(0, course1.get(i).get("name").toString().indexOf('-')));
        }
        for (int i = 0; i < getcourse.size(); i++) {
            name1.add(getcourse.get(i).get("name").toString());
        }
        HashSet h = new HashSet(name);
        name.clear();
        name.addAll(h);
        setAttr("getcourse", name1);
        setAttr("coures_name", name);
        render("/view/yuejuan/yuejuan.jsp");
    }


    public void yijianyuejuan() {
        String type = getPara("yj");
        OutputStream outPutStream = null;
        List<PaperConfig> paperConfigList = null;
        // 获取所有参考的学生，没在这个list里面的学生为缺考状态。
        List<Student> studentList = null;
        // 学生分数集合
        List<StudentScore> studentScoreList = null;
        YuejuanService yuejuanService = null;
        try {
            yuejuanService = new YuejuanService();
            paperConfigList = yuejuanService.getAllPaperConfig();
            if (type.equals("0")) {
                String sclass = java.net.URLDecoder.decode(getPara("sclass"), "UTF-8");
                studentList = yuejuanService.getStudentBySclass(sclass);
            } else if (type.equals("1")) {
                // 如果是一键阅卷全部学生的，进入这个方法。。
                studentList = yuejuanService.getAllStudent();
            }
            if (studentList == null || studentList.size() == 0) {
            } else {
                for (int i = 0; i < studentList.size(); i++) {
                    // 得到一个学生的所有studentPaper，并且为每一个学生的试卷进行阅卷
                    yuejuanService.DoneOneStudent((String) studentList.get(i).get("sno"), paperConfigList);
                }
                // 上面for循环走完的时候应该是所有的题目都做过评分了，下面要做的是汇总成绩，按照学生来。
                studentScoreList = new ArrayList<StudentScore>();
                for (int i = 0; i < studentList.size(); i++) {
                    String sno = studentList.get(i).get("sno");
                    StudentScore studentScore = new StudentScore();
                    studentScore.setName((String) studentList.get(i).get("name"));
                    studentScore.setSchool((String) studentList.get(i).get("school"));
                    studentScore.setSno((String) studentList.get(i).get("sno"));
                    List<ScoreBean> scoreBeanList = new ArrayList<ScoreBean>();
                    for (int j = 0; j < paperConfigList.size(); j++) {
                        double score = yuejuanService.DoneOneScore(sno, paperConfigList.get(j));
                        if (score != -1) {
                            ScoreBean scoreBean = new ScoreBean();
                            scoreBean.setAllScore(score);
                            scoreBean.setCourseName((String) paperConfigList.get(j).getCourse().get("name"));
                            scoreBeanList.add(scoreBean);
                        }
                    }
                    studentScore.setScoreBeanList(scoreBeanList);
                    studentScoreList.add(studentScore);
                }
            }
        } catch (Exception e) {

        }
        try {
            File file = new File("score.xlsx");
            outPutStream = new FileOutputStream(file);
            // 新建一个Excel
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet
            HSSFRow row0 = sheet.createRow(0);

            HSSFCell schoolCell = row0.createCell(0);
            schoolCell.setCellValue(new HSSFRichTextString("学校"));
            HSSFCell snoCell = row0.createCell(1);
            snoCell.setCellValue(new HSSFRichTextString("考号"));
            HSSFCell snameCell = row0.createCell(2);
            snameCell.setCellValue(new HSSFRichTextString("学生姓名"));

            if (studentList == null || studentList.size() == 0) {

            } else {
                HSSFCell cell1 = row0.createCell(studentScoreList.get(0)
                        .getScoreBeanList().size() + 3);
                cell1.setCellValue(new HSSFRichTextString("总分"));

                for (int i = 0; i < studentScoreList.get(0).getScoreBeanList()
                        .size(); i++) {
                    ScoreBean scoreBean = studentScoreList.get(0)
                            .getScoreBeanList().get(i);
                    HSSFCell cell = row0.createCell(i + 3);
                    System.out.println(scoreBean.getCourseName());
                    cell.setCellValue(new HSSFRichTextString(scoreBean
                            .getCourseName()));
                }

                for (int i = 0; i < studentScoreList.size(); i++) {
                    int zongFen = 0;
                    HSSFRow row = sheet.createRow(i + 1);
                    StudentScore studentScore = studentScoreList.get(i);

                    HSSFCell schCell = row.createCell(0);
                    schCell.setCellValue(new HSSFRichTextString(studentScore
                            .getSchool()));
                    HSSFCell snCell = row.createCell(1);
                    snCell.setCellValue(new HSSFRichTextString(studentScore
                            .getSno()));
                    HSSFCell nameCell = row.createCell(2);
                    nameCell.setCellValue(new HSSFRichTextString(studentScore
                            .getName()));

                    for (int j = 0; j < studentScore.getScoreBeanList().size(); j++) {
                        HSSFCell cell = row.createCell(j + 3);
                        ScoreBean scoreBean = studentScore.getScoreBeanList()
                                .get(j);
                        zongFen += scoreBean.getAllScore();
                        cell.setCellValue(new HSSFRichTextString(scoreBean
                                .getAllScore() + ""));
                    }
                    HSSFCell cell2 = row.createCell(studentScore.getScoreBeanList()
                            .size() + 3);
                    cell2.setCellValue(new HSSFRichTextString(zongFen + ""));
                }
            }

            workbook.write(outPutStream);
            renderFile(file);
        } catch (IOException e1) {
            e1.printStackTrace();
        } finally {
            try {
                outPutStream.flush();
                outPutStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * @Author:HeXun
     * @Description: 导出缺考名单功能
     * @Date: 2017/8/4 0004
     */
    public void exportAbsentStudents() {
        List<Student> stuList = Student.dao.find("select * from student st,(select DISTINCT sno from Studentexam where starttime is null) s where st.sno = s.sno");
        //创建excel对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row0 = sheet.createRow(0);
        // ===书写第一行=====
        String[] strs = {"学校", "年级", "考号", "姓名"};
        for (int i = 0; i < 4; i++) {
            HSSFCell cell0 = row0.createCell(i);
            cell0.setCellValue(new HSSFRichTextString(strs[i]));
        }
        // ===书写第一行END=====
        for (int i = 0; i < stuList.size(); i++) {
            Student student = stuList.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            HSSFCell cell1 = row.createCell(0);
            HSSFCell cell2 = row.createCell(1);
            HSSFCell cell3 = row.createCell(2);
            HSSFCell cell4 = row.createCell(3);
            cell1.setCellValue(new HSSFRichTextString(student.get("school") + ""));
            cell2.setCellValue(new HSSFRichTextString(student.get("sclass").toString()));
            cell3.setCellValue(new HSSFRichTextString(student.get("sno").toString()));
            cell4.setCellValue(new HSSFRichTextString(student.get("name").toString()));
        }
        //创建文件对象
        File file = new File("absent.xls");
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(file);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        renderFile(file);
        file.delete();
    }

    /**
     * @Author:HeXun
     * @Description: 成绩发布
     * @Date: 2017/8/4 0004
     */
    public void publicScores() {
        List<Course> courses = Course.dao.find("select * from courses");
        List<HashMap<String, String>> scores = new ArrayList<HashMap<String, String>>();
        for (Course course : courses) {
            HashMap<String, String> map = new HashMap<String, String>();
            int num = yuejuanService.markCount(Integer.parseInt(course.get("id").toString()));
            if (num > 0) {
                int isPublic = yuejuanService.courseScore(Integer.parseInt(course.get("id").toString()));
                map.put("course", course.get("name").toString());
                map.put("isPublic", String.valueOf(isPublic));
                map.put("course_id", course.get("id").toString());
                scores.add(map);
            }
        }
        setAttr("scoreList", scores);
        render("showScores.jsp");
    }

    /**
     * @Author:HeXun
     * @Description: 发布成绩或取消发布
     * @Date: 2017/8/4 0004
     */
    public void updatePublic() {
        String course_id = getPara("course_id");
        String status = getPara("isPublic");
        if (course_id == null || "".equals(course_id) || status == null || "".equals(status)) {
            renderText("您的操作有误!");
        } else {
            boolean result = yuejuanService.updataStatus(course_id, status);
            if (result && status.equals("0")) {
                renderText("取消发布成功!");
            } else if (result && status.equals("1")) {
                renderText("发布成功!");
            } else {
                renderText("操作失败。。");
            }
        }
    }

    public void GetScore() {

        String[] desc = {"学校", "考号", "姓名", "选择", "判断", "简单填空", "阅读选择", "简答", "多选",
                "复杂填空", "总分"};
        OutputStream outputStream = null;
        try {
            String course_name = java.net.URLDecoder.decode(getPara("getcourses"), "UTF-8");
            List<Course> courseid = Course.dao.find("select * from courses where courses.name='" + course_name + "'");
            String sql = "select school,sno,name,choice,judge,simplefill,choiceread,simplepress,choicefill,complex,allscore from score ,student where score.stuno=student.sno and score.course="
                    + Integer.parseInt(courseid.get(0).get("id").toString());
            List<Record> getcourses = Db.find(sql);
            File file = new File("Score.xlsx");
            outputStream = new FileOutputStream(file);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet();
            HSSFRow row0 = sheet.createRow(0);
            // ===书写第一行=====
            for (int i = 0; i < 11; i++) {
                HSSFCell cell0 = row0.createCell(i);
                cell0.setCellValue(new HSSFRichTextString(desc[i]));
            }
            // ===书写第一行END=====
            for (int i = 0; i < getcourses.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                HSSFCell cell1 = row.createCell(0);
                HSSFCell cell2 = row.createCell(1);
                HSSFCell cell3 = row.createCell(2);
                HSSFCell cell4 = row.createCell(3);
                HSSFCell cell5 = row.createCell(4);
                HSSFCell cell6 = row.createCell(5);
                HSSFCell cell7 = row.createCell(6);
                HSSFCell cell8 = row.createCell(7);
                HSSFCell cell9 = row.createCell(8);
                HSSFCell cell10 = row.createCell(9);
                HSSFCell cell11 = row.createCell(10);
                cell1.setCellValue(new HSSFRichTextString(getcourses.get(i).getStr("school").toString()));
                cell2.setCellValue(new HSSFRichTextString(getcourses.get(i).getStr("sno").toString()));
                cell3.setCellValue(new HSSFRichTextString(getcourses.get(i).getStr("name").toString()));
                cell4.setCellValue(new HSSFRichTextString(getcourses.get(i).getStr("choice").toString()));
                cell5.setCellValue(new HSSFRichTextString(getcourses.get(i).getStr("judge").toString()));
                cell6.setCellValue(new HSSFRichTextString(getcourses.get(i).getStr("simplefill").toString()));
                cell7.setCellValue(new HSSFRichTextString(getcourses.get(i).getStr("choiceread").toString()));
                cell8.setCellValue(new HSSFRichTextString(getcourses.get(i).getStr("simplepress").toString()));
                cell9.setCellValue(new HSSFRichTextString(getcourses.get(i).getStr("choicefill").toString()));
                cell10.setCellValue(new HSSFRichTextString(getcourses.get(i).getStr("comlex").toString()));
                cell11.setCellValue(new HSSFRichTextString(getcourses.get(i).getStr("allscore").toString()));
            }
            workbook.write(outputStream);
            renderFile(file);
        } catch (IOException e1) {
            e1.printStackTrace();
        } finally {
            try {
                outputStream.flush();
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public void SeachScore() {
        int page = 1;
        String course_n = "";
        String sclass = "";
        String sno = "";
        String name = "";
        String sql = null;
        if (getPara("page") != null) {
            page = Integer.parseInt(getPara("page"));
        }
        if (getPara("courses_n") != null) {
            course_n = getPara("courses_n");
        }
        if (getPara("sclass1") != null) {
            sclass = getPara("sclass1");
        }
        if (getPara("stno") != null) {
            sno = getPara("stno");
        }
        if (getPara("stname") != null) {
            name = getPara("stname");
        }
        int size = ConstData.SIZE;
        List<Course> course_nu = Course.dao.find("select * from courses where name='" + course_n + "'");
        int course_number = Integer.parseInt(course_nu.get(0).get("id").toString());
        sql = " from score,student,courses WHERE score.course LIKE '%" +
                course_number + "%' and student.sno like '%" + sno + "%' and student.name like '%" +
                name + "%' and student.sclass like '%" + sclass + "%'and score.studentno=student.sno ";
        Page<Record> scorePageList = Db.paginate(page, size, "select DISTINCT score.*,student.name,student.sclass", sql);
        setAttr("pageurl", "/yuejuan/SeachScore?courses_n=" + course_n +
                "&sclass1=" + sclass + "&stno=" + sno + "&stname=" + name);
        setAttr("Page", scorePageList);
        setAttr("page", page);
        showyuejian();
    }
}
